package tom.server;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import tom.client.admin.Service;
import tom.shared.AppUser;
import tom.shared.Company;
import tom.shared.Program;
import tom.shared.PostCode;

public class AdminServiceImpl extends SessionService implements Service {

	private static final long serialVersionUID = -8441486422501703954L;

	@Override
	public boolean updateAppUser(String userName, String fullName,
			String descript, String comCode, Integer level) {
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			if (comCode != null && !comCode.equals("")) {
				stmt = conn
						.prepareStatement("insert into working values(?, ?)");
				stmt.setString(1, userName);
				stmt.setString(2, comCode);
				rowEffect = stmt.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			stmt = conn
					.prepareStatement("update appuser set fullname=?,descript=?,comcode=?,level=? where username=?");

			stmt.setString(1, fullName);
			stmt.setString(2, descript);
			stmt.setString(3, comCode);
			stmt.setInt(4, level);
			stmt.setString(5, userName);
			rowEffect = stmt.executeUpdate();
			log(getSessVar("user") + " update " + userName);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rowEffect > 0;
	}

	@Override
	public boolean updatePassword(String userName, String oldPswd,
			String newPswd) {
		Connection conn = super.getDatabase().getConnection();
		String loginUser = this.getSessVar("user");
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			ResultSet rs = super.getDatabase().executeQuery(
					"select passwd from appuser where username='" + userName
							+ "'");
			String storePasswd = null;
			if (rs.next()) {
				storePasswd = rs.getString(1);
				if (storePasswd == null
						|| storePasswd.equals(oldPswd)
						|| (loginUser.equals("admin") && !loginUser
								.equals(userName))) {
					stmt = conn
							.prepareStatement("update appuser set passwd=? where username=?");
					stmt.setString(1, newPswd);
					stmt.setString(2, userName);
					rowEffect = stmt.executeUpdate();
				}
				log(loginUser + " chg password " + userName);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rowEffect > 0;
	}

	@Override
	public boolean updatePassword2(String userName, String oldPswd,
			String newPswd) {
		Connection conn = super.getDatabase().getConnection();
		String loginUser = this.getSessVar("user");
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			ResultSet rs = super.getDatabase().executeQuery(
					"select passwd2 from appuser where username='" + userName
							+ "'");
			String storePasswd = null;
			if (rs.next()) {
				storePasswd = rs.getString(1);
				if (storePasswd == null
						|| storePasswd.equals(oldPswd)
						|| (loginUser.equals("admin") && !loginUser
								.equals(userName))) {
					stmt = conn
							.prepareStatement("update appuser set passwd2=? where username=?");
					stmt.setString(1, newPswd);
					stmt.setString(2, userName);
					rowEffect = stmt.executeUpdate();
				}
				log(loginUser + " chg password2 " + userName);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rowEffect > 0;
	}

	@Override
	public boolean deleteAppUser(String userName) {
		int rowEffect = 0;
		rowEffect = super.getDatabase().executeUpdate(
				"delete from appuser where username='" + userName + "'");
		log(getSessVar("user") + " delete user " + userName);
		return rowEffect > 0;
	}

	@Override
	public AppUser selectMyInfo(String userName) {
		// String user = this.getSessionVar("user");
		String sql = "select * from appuser where username='" + userName + "'";
		ResultSet res = getDatabase().executeQuery(sql);
		AppUser record = null;
		try {
			if (res.next())
				record = new AppUser(res.getString(1), res.getString(2),
						res.getString(3), res.getDate(4), res.getString(5),
						res.getString(6), res.getInt(7));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return record;
	}

	@Override
	public boolean createUser(String userName, String fullName,
			String descript, String comCode, Integer level) {
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			stmt = conn
					.prepareStatement("insert into appuser(username, fullname, descript, created) values (?,?,?,curdate())");
			stmt.setString(1, userName);
			stmt.setString(2, fullName);
			stmt.setString(3, descript);
			rowEffect = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rowEffect > 0;
	}

	@Override
	public boolean deleteCompany(String comCode) {
		int rowEffect = 0;
		rowEffect = super.getDatabase().executeUpdate(
				"delete from company where comCode='" + comCode + "'");
		log(super.getSessVar("user") + " delete company " + comCode);
		return rowEffect > 0;
	}

	@Override
	public boolean insertCompany(Company record) {
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			stmt = conn
					.prepareStatement("insert into company values (?,?,?,?,?,?,?,?,?,?,?,?)");
			stmt.setString(1, record.getComCode());
			stmt.setString(2, record.getName());
			stmt.setString(3, record.getAddress());
			stmt.setString(4, record.getTelephone());
			stmt.setObject(5, record.getInvoiceNo());
			stmt.setObject(6, record.getItemNo());
			stmt.setObject(7, record.getCashInvoiceNo());
			stmt.setObject(8, record.getCreditInvoiceNo());
			stmt.setObject(9, record.getPaymentNo());
			stmt.setObject(10, record.getPurchaseNo());
			stmt.setObject(11, record.getVoucherNo());
			stmt.setObject(12, record.getVouRecieptNr());
			stmt.setObject(13, record.getVouServRecieptNr());
			stmt.setObject(14, record.getGlBookingNo());
			stmt.setObject(15, record.getContactNo());
			stmt.setObject(16, record.getDefaultVat());
			rowEffect = stmt.executeUpdate();
			log(getSessVar("user") + " insert company " + record.getComCode());
		} catch (SQLException e) {
			log(e.getMessage());
		}
		return rowEffect > 0;
	}

	@Override
	public boolean updateCompany(Company record) {
		System.out.println("update Company!");
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			log(getSessVar("user") + " update company " + record.getComCode());
			stmt = conn
					.prepareStatement("update company set name=?, address=?, telephone=?, invoiceNo=?, "
							+ "cashInvoiceNr=?, creditInvoiceNr=?, paymentNr=?, purchaseNr=?, vouPurchaseNr=?, "
							+ "glBookingNr=?, contactNr=? where comCode=?");
			stmt.setString(1, record.getName());
			stmt.setString(2, record.getAddress());
			stmt.setString(3, record.getTelephone());
			stmt.setObject(4, record.getInvoiceNo());
			stmt.setObject(5, record.getCashInvoiceNo());
			stmt.setObject(6, record.getCreditInvoiceNo());
			stmt.setObject(7, record.getPaymentNo());
			stmt.setObject(8, record.getPurchaseNo());
			stmt.setObject(9, record.getVoucherNo());
			stmt.setObject(10, record.getGlBookingNo());
			stmt.setObject(11, record.getContactNo());
			stmt.setString(12, record.getComCode());
			rowEffect = stmt.executeUpdate();

		} catch (SQLException e) {
			log(e.getMessage());
		}
		return rowEffect > 0;
	}

	@Override
	public List<String> selectModule() {
		String sql = "select distinct(module) from program";
		List<String> list = null;
		try {
			ResultSet res = getDatabase().executeQuery(sql);
			list = new ArrayList<String>();
			while (res.next()) {
				list.add(res.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public Program selectForm(String pgmId) {
		String sql = "select * from program where pgmId = '" + pgmId + "'";
		Program record = null;
		try {
			ResultSet res = getDatabase().executeQuery(sql);

			while (res.next()) {
				record = new Program();
				record.pgmId = res.getString(1);
				record.pgmName = res.getString(2);
				record.descript = res.getString(3);
				record.module = res.getString(4);
				record.active = res.getBoolean(5);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return record;
	}

	@Override
	public boolean deleteForm(String pgmId) {
		int rowEffect = 0;
		String sql = "delete from program where pgmId='" + pgmId + "'";
		log(sql);
		rowEffect = super.getDatabase().executeUpdate(sql);
		log(super.getSessVar("user") + " delete program " + pgmId);
		return rowEffect > 0;
	}

	@Override
	public boolean insertForm(Program record) {
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			stmt = conn
					.prepareStatement("insert into program values (?,?,?,?,?)");
			stmt.setString(1, record.pgmId);
			stmt.setString(2, record.pgmName);
			stmt.setString(3, record.descript);
			stmt.setString(4, record.module);
			stmt.setBoolean(5, record.active);
			rowEffect = stmt.executeUpdate();
			log(getSessVar("user") + " insert program " + record.pgmId);
		} catch (SQLException e) {
			log(e.getMessage());
		}
		return rowEffect > 0;
	}

	@Override
	public boolean updateForm(Program record) {
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			stmt = conn
					.prepareStatement("update program set pgmName=?, descript=?, module=?, active=? where pgmId=?");
			stmt.setString(1, record.pgmName);
			stmt.setString(2, record.descript);
			stmt.setString(3, record.module);
			stmt.setBoolean(4, record.active);
			stmt.setString(5, record.pgmId);
			rowEffect = stmt.executeUpdate();
			log(getSessVar("user") + " update program " + record.pgmId);
		} catch (SQLException e) {
			log(e.getMessage());
		}
		return rowEffect > 0;
	}

	@Override
	public boolean deleteWorking(String userName, String comCode) {
		int rowEffect = 0;
		rowEffect = super.getDatabase().executeUpdate(
				"delete from working where userName='" + userName
						+ "' and comCode='" + comCode + "'");
		log(super.getSessVar("user") + " delete working " + userName + "-"
				+ comCode);
		return rowEffect > 0;
	}

	@Override
	public List<String> selectWorking(String userName) {
		String sql = "select comCode from working where userName='" + userName
				+ "'";
		List<String> list = null;

		ResultSet res = getDatabase().executeQuery(sql);
		list = new ArrayList<String>();
		try {
			while (res.next()) {
				list.add(res.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Program> selectPermission(String userName, String comCode) {
		String sql = null;
		Database db = getDatabase();
		sql = "select level from appuser where userName='" + userName + "'";
		ResultSet res = db.executeQuery(sql);
		int level = 0;
		try {
			if (res.next())
				level = res.getInt(1);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		if (level < 9)
			sql = "select program.pgmId, program.pgmName, program.descript, "
					+ "program.module, permission.level from program, permission"
					+ " where permission.userName='" + userName
					+ "' and permission.pgmId = program.pgmId and "
					+ "permission.comCode='" + comCode + "'";

		else
			sql = "select program.pgmId, program.pgmName, program.descript, "
					+ "program.module, 9 as 'level' from program";
		List<Program> list = new ArrayList<Program>();
		res = db.executeQuery(sql);
		try {
			while (res.next()) {
				Program f = new Program();
				f.pgmId = res.getString(1);
				f.pgmName = res.getString(2);
				f.descript = res.getString(3);
				f.module = res.getString(4);
				f.level = res.getInt(5);
				list.add(f);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public boolean deletePermission(String userName, String comCode,
			String pgmId) {
		int rowEffect = 0;
		rowEffect = super.getDatabase().executeUpdate(
				"delete from permission where userName='" + userName
						+ "' and comCode='" + comCode + "' and pgmId='" + pgmId
						+ "'");
		log(super.getSessVar("user") + " delete Permission " + userName + "-"
				+ comCode + '-' + pgmId);
		return rowEffect > 0;
	}

	@Override
	public boolean insertPermission(String userName, String comCode,
			String pgmId, Integer level) {
		int rowEffect = 0;
		rowEffect = super.getDatabase().executeUpdate(
				"insert into permission value('" + userName + "','" + comCode
						+ "','" + pgmId + "'," + level + ")");
		log(super.getSessVar("user") + " insert Permission " + userName + "-"
				+ comCode + "-" + pgmId);
		return rowEffect > 0;
	}

	@Override
	public List<Program> selectAvailForm(String userName, String comCode) {
		List<Program> list = new ArrayList<Program>();
		if (!userName.equals("admin")) {
			String sql = "select * from program where pgmId not in "
					+ "( select pgmId from permission where userName = '"
					+ userName + "' and " + "permission.comCode='" + comCode
					+ "')";
			ResultSet res = getDatabase().executeQuery(sql);
			try {
				while (res.next()) {
					Program f = new Program();
					f.pgmId = res.getString(1);
					f.pgmName = res.getString(2);
					f.descript = res.getString(3);
					f.module = res.getString(4);
					list.add(f);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	@Override
	public Company selectCompany(String comCode) {
		String sql = "select * from company where comCode='" + comCode + "'";
		Company record = null;

		try {
			ResultSet res = getDatabase().executeQuery(sql);
			while (res.next()) {
				record = new Company();
				record.setComCode(res.getString(1));
				record.setName(res.getString(2));
				record.setAddress(res.getString(3));
				record.setTelephone(res.getString(4));
				record.setInvoiceNo((Integer) res.getObject(5));
				record.setCashInvoiceNo((Integer) res.getObject(6));
				record.setCreditInvoiceNo((Integer) res.getObject(7));
				record.setPaymentNo((Integer) res.getObject(8));
				record.setVoucherNo((Integer) res.getObject(9));
				record.setPurchaseNo((Integer) res.getObject(10));
				record.setGlBookingNo((Integer) res.getObject(11));
				record.setContactNo((Integer) res.getObject(12));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return record;
	}

	@Override
	public PostCode selectPostCode(Integer id) {
		String sql = "select * from postcode where id=" + id;
		PostCode record = null;
		try {
			ResultSet res = getDatabase().executeQuery(sql);
			while (res.next()) {
				record = new PostCode(res.getInt(1), res.getString(2),
						res.getString(3), res.getString(4), res.getString(5),
						res.getString(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return record;
	}

	@Override
	public boolean deletePostCode(Integer id) {
		int rowEffect = 0;
		rowEffect = super.getDatabase().executeUpdate(
				"delete from postcode where id=" + id.toString());
		log(super.getSessVar("user") + " delete PostCode " + id.toString());
		return rowEffect > 0;
	}

	@Override
	public boolean insertPostCode(PostCode record) {
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			stmt = conn
					.prepareStatement("insert into postcode values (?,?,?,?,?)");
			stmt.setInt(1, record.id);
			stmt.setString(2, record.postCode);
			stmt.setString(3, record.amphur);
			stmt.setString(4, record.province);
			stmt.setString(5, record.note);
			stmt.setString(6, record.postOffice);
			rowEffect = stmt.executeUpdate();
			log(super.getSessVar("user") + " insert PostCode "
					+ record.id.toString());
		} catch (SQLException e) {
			log(e.getMessage());
		}
		return rowEffect > 0;
	}

	@Override
	public boolean updatePostCode(PostCode record) {
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			stmt = conn
					.prepareStatement("update postcode set postCode=?, amphur=?, province=?, "
							+ "note=?, postOffice=? where id=?");
			stmt.setString(1, record.postCode);
			stmt.setString(2, record.amphur);
			stmt.setString(3, record.province);
			stmt.setString(4, record.note);
			stmt.setString(5, record.postOffice);
			stmt.setInt(6, record.id);
			rowEffect = stmt.executeUpdate();
			log(super.getSessVar("user") + " update PostCode "
					+ record.id.toString());
		} catch (SQLException e) {
			log(e.getMessage());
		}
		return rowEffect > 0;
	}

	@Override
	public boolean insertWorking(String userName, String comCode) {
		int rowEffect = 0;
		rowEffect = super.getDatabase().executeUpdate(
				"insert into working values ('" + userName + "','" + comCode
						+ "')");
		log(super.getSessVar("user") + " insert working " + userName + "-"
				+ comCode);
		return rowEffect > 0;
	}

}
